package com.wmx.jdbc_template_app.controller;

import cn.hutool.core.util.ArrayUtil;
import com.wmx.jdbc_template_app.pojo.Person;
import com.wmx.jdbc_template_app.utils.ResultCode;
import com.wmx.jdbc_template_app.utils.ResultData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * JdbcTemplate + mysql 数据库增删改操作
 * 批处理，批量操作。
 *
 * @author wangmaoxoing
 */
@RestController
@SuppressWarnings("Duplicates")
public class PersonController {

    private static final Logger logger = LoggerFactory.getLogger(PersonController.class);

    /**
     * JdbcTemplate 用于简化 JDBC 操作，还能避免一些常见的错误，如忘记关闭数据库连接
     * Spring Boot 默认提供了数据源与 org.springframework.jdbc.core.JdbcTemplate
     * spring boot 默认对 JdbcTemplate 的配置，已经注入了数据源创建好了实例，程序员直接获取使用即可
     */
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 创建 person 表
     * http://localhost:8080/person/createTable
     *
     * @return
     */
    @GetMapping("person/createTable")
    public String createTable() {
        String sql = "create table if not EXISTS person(" +
                "pId int primary key auto_increment," +
                "pName varchar(18) not null," +
                "birthday date not null," +
                "salary float(10,2)," +
                "summary varchar(256)" +
                ")";
        //execute 可以执行任意 sql(不宜做查询)
        jdbcTemplate.execute(sql);
        return sql;
    }

    /**
     * 删除 person 表
     * http://localhost:8080/person/dropTable
     *
     * @return
     */
    @GetMapping("person/dropTable")
    public String dropTable() {
        String sql = "DROP TABLE if EXISTS PERSON";
        jdbcTemplate.execute(sql);
        return sql;
    }

    // ----------------------------数据-保存-操作===================./start==============

    /**
     * update(String sql, @Nullable Object... args)
     * 保存用户
     * 为了方便使用的是 get 请求：
     * http://localhost:8080/person/save?pName=admin&summary=重要人物&salary=9999.00
     * 所有 queryForList 方法中的泛型 T 都只能是基本类型，如 Integer.class,String.class等等，如果是 Map 或者 Java Bean 则报错
     *
     * @param person
     * @return
     */
    @GetMapping("person/save")
    public String savePerson(Person person) {
        String message = "保存用户：" + person;
        logger.info(message);
        person.setpName(person.getpName() == null ? "scott" : person.getpName());
        person.setSummary(person.getSummary() == null ? "" : person.getSummary().trim());

        String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
        Object[] params = new Object[4];
        params[0] = person.getpName();
        params[1] = new Date();
        params[2] = person.getSalary();
        params[3] = person.getSummary();
        //update 方法用于执行新增、修改、删除等语句
        jdbcTemplate.update(sql, params);
        return sql;
    }

    /**
     * update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
     * 保存用户————演示获取新增数据在库中ID自动增长生成的主键
     * http://localhost:8080/person/save2
     *
     * @param person
     * @return
     */
    @PostMapping("person/save2")
    public String savePerson2(@RequestBody Person person) {
        String sql = "INSERT INTO PERSON(pId, pName,birthday,salary,summary) VALUES (null,?,?,?,?)";
        // PreparedStatementCreator：提供SQL和任何必要参数的回调，能够提供原生的预编译SQL语句对象(PreparedStatement)
        PreparedStatementCreator statementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                // PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)：使用原生 JDBC 组装预编译SQL语句对象(PreparedStatement)
                // sql：包含一个或者多个占位符 ? 的 Sql 语句。
                // autoGeneratedKeys：指示是否返回数据库自动生成的主键，1 表示是，2表示否。如果不是 INSET 语句，或者不是能过返回自动生成主键的SQL，则忽略此参数。
                PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                // 设置占位符参数的值()
                preparedStatement.setString(1, person.getpName());
                preparedStatement.setDate(2, new java.sql.Date(person.getBirthday().getTime()));
                preparedStatement.setFloat(3, person.getSalary());
                preparedStatement.setString(4, person.getSummary());
                return preparedStatement;
            }
        };
        // 使用默认列表创建新生成的密钥持有者,用于保存自动生成的ID
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(statementCreator, keyHolder);

        // 获取数据自动生成的主键ID
        // Number getKey()：只新增了一行，且只有一列自动增长时进行取值
        // Map<String, Object> getKeys()：只新增了一行，有一列或者多列自动增长时取值，返回值如： {GENERATED_KEY=4}
        // List<Map<String, Object>> getKeyList()：同时新增了多行时取值。
        Number key = keyHolder.getKey();
        Map<String, Object> keys = keyHolder.getKeys();
        List<Map<String, Object>> keyList = keyHolder.getKeyList();

        System.out.println("keys=" + keys);
        System.out.println("keyList=" + keyList);
        return "数据库自动生成的主键ID：" + key;
    }

    /**
     * int execute(Map<String, ?> args)
     * SimpleJdbcInsert 类可以通过JDBC驱动程序检索数据库的元数据，简化配置
     * http://localhost:8080/person/simpleSave1
     *
     * @param person :{"birthday": "1997-08-11", "pName": "李元吉", "salary": 2451.14, "summary": "晋王"}
     */
    @PostMapping("person/simpleSave1")
    public ResultData simpleSave1(@RequestBody Person person) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        // withTableName：指定要操作的数据表
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                // 只对指定的字段进行插入值，未指定的字段不会再插入.
                // .usingColumns(String... columnNames)
                .withTableName("person");
        // 通过Map设置列对应的值
        Map<String, Object> parameters = new HashMap<>(8);
        parameters.put("pName", person.getpName());
        parameters.put("salary", person.getSalary());
        parameters.put("birthday", person.getBirthday());
        parameters.put("summary", person.getSummary());
        int execute = insertPerson.execute(parameters);
        return ResultData.success(execute);
    }

    /**
     * Number executeAndReturnKey(Map<String, ?> args)
     * 1、获取自动生成的主键值
     * http://localhost:8080/person/simpleSave1
     *
     * @param person :{"birthday": "1997-08-11", "pName": "李元吉2", "salary": 2451.14, "summary": "晋王2"}
     * @return
     */
    @PostMapping("person/simpleSave2")
    public ResultData simpleSave2(@RequestBody Person person) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        // withTableName：指定要操作的数据表
        // usingGeneratedKeyColumns：指定自动生成主键列
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                .withTableName("person")
                // 只对指定的字段进行插入值，未指定的字段不会再插入.
                // .usingColumns(String... columnNames)
                .usingGeneratedKeyColumns("pId");
        Map<String, Object> parameters = new HashMap<>(8);
        parameters.put("pName", person.getpName());
        parameters.put("salary", person.getSalary());
        parameters.put("birthday", person.getBirthday());
        parameters.put("summary", person.getSummary());
        // 返回主键值
        Number key = insertPerson.executeAndReturnKey(parameters);
        return ResultData.success(key);
    }

    /**
     * Number executeAndReturnKey(SqlParameterSource parameterSource)
     * 1) 直接通过对象形式，自动去映射，此时java bean中的属性名称要与数据库中的字段名称一一对应
     *
     * @param person: {"birthday": "1997-08-11", "pName": "李元吉3", "salary": 2451.14, "summary": "晋王3"}
     * @return
     */
    @PostMapping("person/simpleSave3")
    public ResultData simpleSave3(@RequestBody Person person) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                .withTableName("person")
                .usingGeneratedKeyColumns("pId");
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(person);
        Number key = insertPerson.executeAndReturnKey(parameters);
        return ResultData.success(key);
    }

    /**
     * Number executeAndReturnKey(SqlParameterSource parameterSource)
     * 1) 定列映射
     *
     * @param person : {"birthday": "1997-08-11", "pName": "李元吉4", "salary": 2451.14, "summary": "晋王4"}
     * @return
     */
    @PostMapping("person/simpleSave4")
    public ResultData simpleSave4(@RequestBody Person person) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                .withTableName("person")
                .usingGeneratedKeyColumns("pId");
        SqlParameterSource parameters = new MapSqlParameterSource()
                // key = 列名
                .addValue("birthday", person.getBirthday())
                .addValue("salary", person.getSalary())
                .addValue("summary", person.getSummary())
                .addValue("pName", person.getpName());
        Number key = insertPerson.executeAndReturnKey(parameters);
        return ResultData.success(key);
    }
    // ----------------------------数据-保存-操作===================./end==============

    /**
     * update(final String sql)
     * 修改用户描述
     * 为了方便使用的是 get 请求：http://localhost:8080/person/update?summary=大咖&pId=1
     *
     * @param person
     * @return
     */
    @GetMapping("person/update")
    public String updatePerson(Person person) {
        String message = "修改用户描述：" + person;
        logger.info(message);
        person.setSummary(person.getSummary() == null ? "" : person.getSummary().trim());
        person.setpId(person.getpId() == null ? 0 : person.getpId());
        StringBuffer sqlBuff = new StringBuffer("UPDATE PERSON SET ");
        //sql 中的字符串必须加单引号
        sqlBuff.append(" SUMMARY='" + person.getSummary() + "' ");
        sqlBuff.append(" WHERE pId=" + person.getpId());
        logger.info("SQL 确认：" + sqlBuff.toString());
        //update 方法用于执行新增、修改、删除等语句
        jdbcTemplate.update(sqlBuff.toString());
        return sqlBuff.toString();
    }

    /**
     * 根据单个 id 删除单条数据：http://localhost:8080/person/deleteById?pId=4
     *
     * @param pId
     * @return
     */
    @GetMapping("person/deleteById")
    public String deletePersonById(Integer pId) {
        String message = "根据 pId 删除：" + pId;
        logger.info(message);
        if (pId == null) {
            return message;
        }
        String sql = "DELETE FROM PERSON WHERE pId = " + pId;
        //execute 同样可以执行任意 DDL 语句
        jdbcTemplate.execute(sql);
        return sql;
    }

    /**
     * 删除整表数据：http://localhost:8080/person/deletesAll
     *
     * @return
     */
    @GetMapping("person/deletesAll")
    public String deleteAll() {
        String sql = "TRUNCATE TABLE PERSON";
        jdbcTemplate.execute(sql);
        return sql;
    }

    // ----------------------------批量操作、批处理、批量操作===================./start============

    /**
     * int[] batchUpdate(final String... sql)：批量执行 sql。返回每一条sql影响的条数。
     * 批量删除
     * http://localhost:8080/person/batchDelete?ids=2,3,4
     *
     * @param ids：多个 id 时用 "," 隔开
     * @return
     */
    @GetMapping("person/batchDelete")
    public ResultData batchDelete(@RequestParam String ids) {
        String message = "删除用户：" + ids;
        logger.info(message);
        if (ids == null || "".equals(ids)) {
            return ResultData.error(ResultCode.PARAM_IS_BLANK);
        }
        String[] id_arr = ids.split(",");
        String[] sql_arr = new String[id_arr.length];
        for (int i = 0; i < id_arr.length; i++) {
            sql_arr[i] = "DELETE FROM PERSON WHERE pId = " + id_arr[i];
        }
        logger.info("SQL 确认：{}", ArrayUtil.toString(sql_arr));
        // batchUpdate 方法用于执行批处理增加删除、修改等 sql
        int[] batchUpdate = jdbcTemplate.batchUpdate(sql_arr);
        return ResultData.success(batchUpdate);
    }

    /**
     * int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
     * * 通过实现特殊接口 BatchPreparedStatementSetter 的两个方法
     * * 并将该实现作为 batchUpdate 方法调用的第二个参数传入，来完成 JdbcTemplate 的批处理。
     * http://localhost:8080/person/batchSave1
     *
     * @param persons ：[{"birthday": "1993-08-25", "pName": "李世民", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave1")
    public ResultData batchSave1(@RequestBody List<Person> persons) {
        String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
        int[] batchUpdate = this.jdbcTemplate.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Person p = persons.get(i);
                        ps.setString(1, p.getpName());
                        ps.setDate(2, p.getBirthday() == null ? null : new java.sql.Date(p.getBirthday().getTime()));
                        ps.setFloat(3, p.getSalary());
                        ps.setString(4, p.getSummary());
                    }

                    public int getBatchSize() {
                        return persons.size();
                    }
                });
        return ResultData.success(batchUpdate);
    }

    /**
     * int[] batchUpdate(String sql, SqlParameterSource[] batchArgs)
     * 1、基于命名参数的批量处理。
     * 2、将要插入的集合通过 SqlParameterSourceUtils#createBatch 转换为 SqlParameterSource 数组，框架内部会自动遍历所有的元素。
     * http://localhost:8080/person/batchSave2
     *
     * @param persons: [{"birthday": "1993-08-25", "pName": "李世民", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave2")
    public ResultData batchSave2(@RequestBody List<Person> persons) {
        // insert into 后面的是表中的字段名称，values 后面的是实体对象中对应的属性名称(不是表中的字段名称)
        String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (:pName, :birthday, :salary, :summary)";
        DataSource dataSource = jdbcTemplate.getDataSource();
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
        int[] batchUpdate = template.batchUpdate(sql, SqlParameterSourceUtils.createBatch(persons));
        return ResultData.success(batchUpdate);
    }

    /**
     * int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss)
     * 1、将一个大的批次划分成多个小的批次执行，每次执行 batchSize 条。
     * 2、返回每个批量影响的条数。
     * http://localhost:8080/person/batchSave3
     *
     * @param persons ： [{"birthday": "1993-08-25", "pName": "李世民3", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸3", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave3")
    public ResultData batchSave3(@RequestBody List<Person> persons) {
        String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
        int[][] batchUpdate = jdbcTemplate.batchUpdate(sql, persons, 10,
                (PreparedStatement ps, Person p) -> {
                    ps.setString(1, p.getpName());
                    ps.setDate(2, p.getBirthday() == null ? null : new java.sql.Date(p.getBirthday().getTime()));
                    ps.setFloat(3, p.getSalary());
                    ps.setString(4, p.getSummary());
                });
        return ResultData.success(batchUpdate);
    }

    /**
     * int[] executeBatch(Map<String, ?>... batch)
     * 1) SimpleJdbcInsert 批量插入, map 中的key对应数据库表中的列
     * http://localhost:8080/person/batchSave4
     *
     * @param persons： [{"birthday": "1993-08-25", "pName": "李世民3", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸3", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave4")
    public ResultData batchSave4(@RequestBody List<Person> persons) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        // withTableName：指定要操作的数据表
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                // 只对指定的字段进行插入值，未指定的字段不会再插入.
                // .usingColumns(String... columnNames)
                .withTableName("person");

        Map[] maps = new Map[persons.size()];
        for (int i = 0; i < persons.size(); i++) {
            Person person = persons.get(i);
            // 通过Map设置列对应的值, key 是数据库表中的列名
            Map<String, Object> parameters = new HashMap<>(8);
            parameters.put("pName", person.getpName());
            parameters.put("salary", person.getSalary());
            parameters.put("birthday", person.getBirthday());
            parameters.put("summary", person.getSummary());
            maps[i] = parameters;
        }
        // 如果表中的列名与实体的属性名称一致,则可以通过下面的方式快速转换
        // List<Map> mapList = cn.hutool.core.bean.BeanUtil.copyToList(persons, Map.class);
        // Map[] maps = cn.hutool.core.util.ArrayUtil.toArray(mapList, Map.class);

        int[] ints = insertPerson.executeBatch(maps);
        return ResultData.success(ints);
    }

    /**
     * int[] executeBatch(SqlParameterSource... batch)
     * 1) 借助 SqlParameterSource 自动映射,此时实体属性名称与表中的列名必须一致.
     * http://localhost:8080/person/batchSave5
     *
     * @param persons： [{"birthday": "1993-08-25", "pName": "李世民3", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸3", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave5")
    public ResultData batchSave5(@RequestBody List<Person> persons) {
        DataSource dataSource = jdbcTemplate.getDataSource();
        // withTableName：指定要操作的数据表
        SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
                // 只对指定的字段进行插入值，未指定的字段不会再插入.
                // .usingColumns(String... columnNames)
                .withTableName("person");
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons);
        int[] ints = insertPerson.executeBatch(batch);
        return ResultData.success(ints);
    }

    /**
     * int[] batchUpdate(String sql, List<Object[]> batchArgs)
     * 1) 批量执行, batchArgs 对应 sql 中的占位符参数值
     * http://localhost:8080/person/batchSave6
     *
     * @param persons： [{"birthday": "1993-08-25", "pName": "李世民3", "salary": 12451.14, "summary": "秦王"}, {"birthday": "1995-03-25", "pName": "李元霸3", "salary": 10431.14, "summary": "赵王"}]
     * @return
     */
    @PostMapping("person/batchSave6")
    public ResultData batchSave6(@RequestBody List<Person> persons) {
        String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
        List<Object[]> batchArgs = new ArrayList<>();
        for (Person person : persons) {
            List<Object> args = new ArrayList<>();
            args.add(person.getpName());
            args.add(person.getBirthday());
            args.add(person.getSalary());
            args.add(person.getSummary());
            batchArgs.add(args.toArray());
        }
        int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
        return ResultData.success(ints);
    }
    // ----------------------------批量操作、批处理、批量操作===================./end==============

}